/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.sapra.dep.conexion;


import java.sql.ResultSet;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.sapra.Prefactura.PrefacturaMO;
import org.sapra.dao.Abono_AdeudoMO;
import org.sapra.dep.CalendarioFacturacion;
import org.sapra.dep.HeaderMO;
import org.sapra.dep.BanorteFile;
import org.sapra.dep.RespDispersionHeader;
import org.sapra.dep.BanorteCuenta;


/**
 *
 * @Luis Guerrero
 */
public class DAC_Insert extends DAC{

    public DAC_Insert(){

    }

             public void deleteCalFacturacion( CalendarioFacturacion cf){
             String sql="DELETE FROM ";

             try {
                 Class.forName("com.mysql.jdbc.Driver").newInstance();
                 conn = (Connection) DriverManager.getConnection(url);
                 stmt = (Statement) conn.createStatement();
                 sql="DELETE FROM calendariofacturacionpracticantes WHERE calendarioFacturacionPracticantesId="+cf.getCalendarioFacturacionPracticantesId();
                 stmt.executeUpdate(sql);
                 System.out.println(sql);
                 CloseConnexion();
             }catch(Exception e){
                        e.printStackTrace();
                        CloseConnexion();
                }
         }
                  public boolean insertarFecha(ArrayList<CalendarioFacturacion> lista,int empresaId){
		boolean bandera=false;

             try{

                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = (Connection) DriverManager.getConnection(url);
                stmt = (Statement) conn.createStatement();
                 for(int i=0;i<lista.size();i++){
                    CalendarioFacturacion m=new CalendarioFacturacion();
                    m=(CalendarioFacturacion)lista.get(i);
                    if(m==null){}else{
                     String sql = "INSERT INTO caintra.calendariofacturacionpracticantes (dia_pago,dia_dispersion,dia_gap,dia_limite_aut_fac,";
                     sql+="dias_a_pagar_periodo,fk_empresaId,dia_inicio,dia_fin)values(";

                     if(m.getDia_pago()==null||m.getDia_pago().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00',";
                     }else{
                         sql+="'"+m.getDia_pago()+"',";
                     }

                     if(m.getDia_dispersion()==null||m.getDia_dispersion().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                     }else{
                         sql+="'"+m.getDia_dispersion()+"',";
                     }

                    if(m.getDia_gap()==null||m.getDia_gap().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_gap()+"',";
                    }

                      if(m.getDia_limite_aut_fac()==null||m.getDia_limite_aut_fac().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_limite_aut_fac()+"',";
                    }

                      if(m.getDias_a_pagar_periodo()==null||m.getDias_a_pagar_periodo().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+=0+",";
                    } else{
                         sql+=m.getDias_a_pagar_periodo()+",";
                    }
                     
                     //sql+="'"+m.getDia_limite_aut_fac()+"',";
                     //sql+=m.getDias_a_pagar_periodo()+",";
                     sql+=empresaId+",";
                     //sql+="'"+m.getDia_inicio()+"',";
                   if(m.getDia_inicio()==null||m.getDia_inicio().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_inicio()+"',";
                    }

                     //sql+="'"+m.getDia_fin()+"');";

                   if(m.getDia_fin()==null||m.getDia_fin().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00'"+");";
                    } else{
                         sql+="'"+m.getDia_fin()+"');";
                    }
                     System.out.println(sql);
                     stmt.executeUpdate(sql);
                     bandera=true;}
                    }


                CloseConnexion();
		}catch(Exception exc)
                {
                    CloseConnexion();
                    exc.printStackTrace();
                    bandera=false;
                }finally {
                     CloseConnexion();
                     }
                return bandera;
	}

                  /*public boolean insertarFecha(ArrayList<CalendarioFacturacion> lista,int empresaId, ArrayList<CalendarioFacturacion> listaaut){
		boolean bandera=false;

             try{
                 CalendarioFacturacion fec_aut=new CalendarioFacturacion();

                 for(int i=0;i<listaaut.size();i++){
                 fec_aut=listaaut.get(i);
                 }
                 SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
                 Date per_actual = new Date();
                 per_actual=formato.parse(fec_aut.getDia_fin());

                 for(int i=0;i<lista.size();i++){
                  Date per = new Date();
                  per=formato.parse(lista.get(i).getDia_fin());
                  if(per.getTime()<per_actual.getTime()){
                      lista.remove(i);
                  }
                 }

                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = (Connection) DriverManager.getConnection(url);
                stmt = (Statement) conn.createStatement();
                 for(int i=0;i<lista.size();i++){
                    CalendarioFacturacion m=new CalendarioFacturacion();
                    m=(CalendarioFacturacion)lista.get(i);
                    if(m==null){}else{
                     String sql = "INSERT INTO caintra.calendariofacturacionpracticantes (dia_pago,dia_dispersion,dia_gap,dia_limite_aut_fac,";
                     sql+="dias_a_pagar_periodo,fk_empresaId,dia_inicio,dia_fin)values(";

                     if(m.getDia_pago()==null||m.getDia_pago().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00',";
                     }else{
                         sql+="'"+m.getDia_pago()+"',";
                     }

                     if(m.getDia_dispersion()==null||m.getDia_dispersion().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                     }else{
                         sql+="'"+m.getDia_dispersion()+"',";
                     }

                    if(m.getDia_gap()==null||m.getDia_gap().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_gap()+"',";
                    }

                      if(m.getDia_limite_aut_fac()==null||m.getDia_limite_aut_fac().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_limite_aut_fac()+"',";
                    }

                      if(m.getDias_a_pagar_periodo()==null||m.getDias_a_pagar_periodo().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+=0+",";
                    } else{
                         sql+=m.getDias_a_pagar_periodo()+",";
                    }

                     //sql+="'"+m.getDia_limite_aut_fac()+"',";
                     //sql+=m.getDias_a_pagar_periodo()+",";
                     sql+=empresaId+",";
                     //sql+="'"+m.getDia_inicio()+"',";
                   if(m.getDia_inicio()==null||m.getDia_inicio().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00', ";
                    } else{
                         sql+="'"+m.getDia_inicio()+"',";
                    }

                     //sql+="'"+m.getDia_fin()+"');";

                   if(m.getDia_fin()==null||m.getDia_fin().trim().equals("")){
                         //sql+="'"+empresaId+"' ,";
                         sql+="'2010-00-00'"+");";
                    } else{
                         sql+="'"+m.getDia_fin()+"');";
                    }
                     System.out.println(sql);
                     stmt.executeUpdate(sql);
                     bandera=true;}
                    }


                CloseConnexion();
		}catch(Exception exc)
                {
                    CloseConnexion();
                    exc.printStackTrace();
                    bandera=false;
                }finally {
                     CloseConnexion();
                     }
                return bandera;
	}*/

        public void insertCompuesta(int opcion,Abono_AdeudoMO m){
             String sql="INSERT INTO ";

             try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = (Connection) DriverManager.getConnection(url);
                stmt = (Statement) conn.createStatement();
                 if(opcion==1)
                     sql+="adeudo_empresa";
                 else
                     sql+="abono_empresa";

                 sql+="(fk_empresaId,fk_periodoId,Cantidad,Comentarios,usuarioId)" +
                         " VALUES("+m.getFk_empresaId()+"," +
                         ""+m.getFk_periodoId()+"," +
                         ""+m.getCantidad()+",'" +
                         ""+m.getComentarios()+"'," +
                         ""+m.getUsuarioId()+");";
                 stmt.executeUpdate(sql);
                 CloseConnexion();
             }catch(Exception ge){
                        CloseConnexion();
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                    CloseConnexion();
                }
        }

                  public void deleteCompuesta(int opcion,int id){
             String sql="DELETE FROM ";

             try {
                 Class.forName("com.mysql.jdbc.Driver").newInstance();
                 conn = (Connection) DriverManager.getConnection(url);
                 stmt = (Statement) conn.createStatement();


                 if(opcion==1)
                     sql+="adeudo_empresa WHERE adeudo_empresaId="+id;
                 else
                     sql+="abono_empresa WHERE abono_empresaId="+id;

                 stmt.executeUpdate(sql);
             }catch(Exception e){
                        e.printStackTrace();
                     }
         }
                  ///METODOS AGREGADOS PARA LA DISPERSION
                  public  void insertHeader(HeaderMO h){
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             try {
                 String sql="";
                 sql="INSERT INTO banorte_headers" +
                         "(idheaders,fecha,estatus,tipo_registro," +
                         "clave_servicio,promotor,nie,razon_social,consecutivo,num_registros,filler)" +
                         "values("+h.getIdHeaders()+"," +
                         "'"+h.getFecha()+"'," +
                         ""+h.getEstatus()+"," +
                         "'"+h.getTipo_registro()+"'," +
                         "'"+h.getClave_servicio()+"'," +
                         "'"+h.getPromotor()+"'," +
                         "'"+h.getNie()+"'," +
                         "'"+h.getRazon_social()+"'," +
                         "'"+h.getConsecutivo()+"'," +
                         "'"+h.getNum_registros()+"'," +
                         "'"+h.getFiller()+"');";

                idRs=pdb.insert(sql);
             }catch(SQLException exs)
                {
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                }catch(Exception ge){
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                       try {
                               pdb.close();
                       }catch(Exception ge1){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                       }
                     }
         }

                  public  void insertFile(BanorteFile f){
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             try {
                 String sql="";

                 sql="INSERT INTO banortefile" +
                         "(IdBanorteFile,NombreArchivo,DireccionArchivo," +
                         "IdBanorte_header,FechaServer,TipoArchivo,Fk_Usuario) " +
                         "VALUES(" +
                         ""+f.getIdBanorteFile()+"" +
                         ",'"+f.getNombreArchivo()+"'"+
                         ",'"+f.getDireccionArchivo()+"'" +
                         ","+f.getIdBanorte_header()+"" +
                         ",'"+f.getFechaServer()+"'" +
                         ","+f.getTipoArchivo()+"" +
                         ","+f.getFk_Usuario()+"" +
                         ");";

                 idRs=pdb.insert(sql);
             }catch(SQLException exs)
                {
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                }catch(Exception ge){
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                       try {
                               pdb.close();
                       }catch(Exception ge1){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                       }
                     }
         }

         public void insertCuenta(int id){
             String sql="";
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             try {
                     sql="INSERT INTO banorte_cuentas(numsiga,fk_practicanteid,numTarjeta,numCuenta)" +
                             "VALUES('0',"+id+",'','');";
                    idRs=pdb.insert(sql);
             }catch(SQLException exs)
                {
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                }catch(Exception ge){
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                       try {
                               pdb.close();
                       }catch(Exception ge1){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                       }
                     }
         }

         //PARA PROBAR LAS ALTAS DE LAS TARJETAS Y LAS CUENTAS EN LA DISPERSION
         public void insertCuenta(int id,String cuenta, String tarjeta){
             String sql="";
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             try {
                     sql="INSERT INTO banorte_cuentas(numsiga,fk_practicanteid,numTarjeta,numCuenta)" +
                             "VALUES('0',"+id+",'"+cuenta+"','"+tarjeta+"');";
                    idRs=pdb.insert(sql);
             }catch(SQLException exs)
                {
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                }catch(Exception ge){
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                       try {
                               pdb.close();
                       }catch(Exception ge1){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                       }
                     }
                System.out.println(sql);
         }



         


         public void insertDispHeader(RespDispersionHeader r){
             String sql="";
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             try {
                 sql="INSERT INTO respdispersionheader(IdRespDispersionHeader,Tiporegistro,ClaveRegistro,Emisora,FechaProceso," +
                         " Consecutivo,NumRegistros,ImporteTotal,NumTotalAlta,ImpTotalAlta," +
                         " NumTotalBaja,ImpTotalBaja,NumTotalCuenta,Accion,fk_usuario)" +
                         "VALUES("+r.getIdRespDispersionHeader()+"" +
                         ",'"+r.getTipoRegistro()+"'" +
                         ",'"+r.getClaveRegistro()+"'" +
                         ",'"+r.getEmisora()+"'" +
                         ",'"+r.getFechaProceso()+"'" +
                         ",'"+r.getConsecutivo()+"'" +
                         ",'"+r.getNumRegistros()+"'" +
                         ",'"+r.getImporteTotal()+"'" +
                         ",'"+r.getNumTotalAlta()+"'" +
                         ",'"+r.getImpTotalAlta()+"'" +
                         ",'"+r.getNumTotalBaja()+"'" +
                         ",'"+r.getImpTotalBaja()+"'" +
                         ",'"+r.getNumTotalCuenta()+"'" +
                         ",'"+r.getAccion()+"'" +
                         ","+r.getFk_usuario()+"" +
                         ");";
                idRs=pdb.insert(sql);
             }catch(SQLException exs)
                {
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                }catch(Exception ge){
                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                }finally {
                       try {
                               pdb.close();
                       }catch(Exception ge1){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                       }
                     }
         }

      public void insertNumCuenta(BanorteCuenta b){
              DBWRAP pdb = new DBWRAP();
                ResultSet superRs;
                int idRs;
             String sql="";
             long numcuenta=0;
             numcuenta=Long.parseLong(b.getNumCuenta());
             int practId=0;
             practId=b.getFk_practicanteId()-100000;
              if(practId>0){
                        try {
                          sql="INSERT INTO banorte_cuentas(numsiga,fk_practicanteid,numTarjeta,numCuenta)" +
                                  "VALUES("+b.getNumSiga()+"" +
                                  ","+b.getFk_practicanteId()+"" +
                                  ",'"+b.getNumTarjeta()+"'" +
                                  ",'"+numcuenta+"')";
                            idRs=pdb.insert(sql);
                     }catch(SQLException exs)
                        {
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, exs);
                        }catch(Exception ge){
                               Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge);
                        }finally {
                               try {
                                       pdb.close();
                               }catch(Exception ge1){
                                       Logger.getLogger(PrefacturaMO.class.getName()).log(Level.SEVERE, null, ge1);
                               }
                             }
                 }
         }

      ////probar borrar calendarios no autorizados
                   /*public void deleteCalFacturacionall( int empid){
             String sql="DELETE FROM ";

             try {
                 Class.forName("com.mysql.jdbc.Driver").newInstance();
                 conn = (Connection) DriverManager.getConnection(url);
                 stmt = (Statement) conn.createStatement();
                 sql="DELETE FROM calendariofacturacionpracticantes WHERE NOT EXISTS (SELECT * FROM factura"
                         + " WHERE calendariofacturacionpracticantes.calendarioFacturacionPracticantesId = factura.fk_periodo) and fk_empresaId="+empid;
                 stmt.executeUpdate(sql);
                 System.out.println(sql);
                 CloseConnexion();
             }catch(Exception e){
                        e.printStackTrace();
                        CloseConnexion();
                }
         }*/

      public void deleteCalFacturacionall(ArrayList<CalendarioFacturacion> listaSinFactura){
             String sql="DELETE FROM ";

             try {
                 Class.forName("com.mysql.jdbc.Driver").newInstance();
                 conn = (Connection) DriverManager.getConnection(url);
                 stmt = (Statement) conn.createStatement();
                 /*sql="DELETE FROM calendariofacturacionpracticantes WHERE NOT EXISTS (SELECT * FROM factura"
                         + " WHERE calendariofacturacionpracticantes.calendarioFacturacionPracticantesId = factura.fk_periodo) and fk_empresaId="+empid;*/
                 for(int i=0; i<listaSinFactura.size();i++){
                     sql="DELETE FROM calendariofacturacionpracticantes WHERE calendarioFacturacionPracticantesId="+listaSinFactura.get(i).getCalendarioFacturacionPracticantesId();
                      stmt.executeUpdate(sql);
                      System.out.println(sql);
                 }
                 //stmt.executeUpdate(sql);
                 //System.out.println(sql);
                 CloseConnexion();
             }catch(Exception e){
                        e.printStackTrace();
                        CloseConnexion();
                }
         }

}



